package com.cho.core.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.cho.core.db.OracleConnection;
import com.cho.core.db.util.DBUtils;
import com.cho.core.vo.ProjectVo;

public class DictionaryDao {
	
	private static Logger logger =  Logger.getLogger("DictionaryDao");
	
	public static List<Map<String, String>> selectTest(ProjectVo vo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = OracleConnection.getConnection(vo);
		
		try {
			String sql = ""
					+ "SELECT 1 TEST      "
					+ "FROM dual       ";
			
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				DBUtils.resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	public static List<Map<String, String>> selectDbAllTables(ProjectVo vo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = OracleConnection.getConnection(vo);
		
		try {
			String sql = ""
					+ "SELECT A.TABLE_NAME, B.COMMENTS               "
					+ "FROM   USER_TABLES A, ALL_TAB_COMMENTS B      "
					+ "WHERE  A.TABLE_NAME = B.TABLE_NAME            "
					+ "  AND  A.STATUS = 'VALID'                     "
					+ "ORDER BY A.TABLE_NAME                         ";
					

			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				DBUtils.resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<Map<String, String>> selectDbAllTableColumns(ProjectVo vo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = OracleConnection.getConnection(vo);
		
		try {
			String sql = ""
						+ "	SELECT                                                       "
						+ "    A.TABLE_NAME,                                             "
						+ "    A.COLUMN_NAME,                                            "
						+ "    A.DATA_TYPE,                                              "
						+ "    A.DATA_LENGTH,                                            "
						+ "    A.NULLABLE,                                               "
						+ "    B.COMMENTS                                                "
						+ "FROM USER_TAB_COLUMNS A, ALL_COL_COMMENTS B, USER_TABLES C    "
						+ "WHERE A.TABLE_NAME = C.TABLE_NAME                             "
						+ "AND A.COLUMN_NAME = B.COLUMN_NAME                             "
						+ "AND C.TABLE_NAME = B.TABLE_NAME                               "
						+ "AND OWNER = ?		                                         ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getDbId().toUpperCase());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				DBUtils.resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
/*
					+ "SELECT                                        "
					+ "    A.TABLE_NAME,                             "
					+ "    A.COLUMN_NAME,                            "
					+ "    A.DATA_TYPE,                              "
					+ "    A.DATA_LENGTH,                            "
					+ "    A.NULLABLE,                               "
					+ "    B.COMMENTS                                "
					+ "FROM USER_TAB_COLUMNS A, ALL_COL_COMMENTS B   "
					+ "WHERE A.COLUMN_NAME = B.COLUMN_NAME(+)        "
					+ "AND OWNER  IN (?)                        	 ";
SELECT 
    A.TABLE_NAME,
    A.COLUMN_NAME,
    A.DATA_TYPE,
    A.DATA_LENGTH,
    A.NULLABLE,
    B.COMMENTS
FROM USER_TAB_COLUMNS A, ALL_COL_COMMENTS B, USER_TABLES C
WHERE a.table_name = c.table_name
and A.COLUMN_NAME = B.COLUMN_NAME
and c.table_name = b.table_name
AND OWNER  IN ('TPM')
 * */
	
	public static void main(String[] args) {
		DictionaryDao t = new DictionaryDao();
		
		List<Map<String, String>> list = DictionaryDao.selectDbAllTableColumns(null);
		
		for (Map<String, String> map : list) {
			System.out.println("TABLE:"+map.get("TABLE_NAME") + ", " + map.get("COMMENTS"));
		}
	}
}
